const mysql = require('mysql')
const fs = require('fs')
const db = mysql.createPool({
    host: 'phpmyadmin.qieqiong.com', //数据库的IP地址
    user: 'mysql9779149', //登录数据库的账号
    password: 'jdUvSoMYoF', //登录数据库的密码
    database: 'mysql9779149_db', //指定要操作哪个数据库
})

exports.upload = (req, res) => {
    fs.rename(req.file.path, "uploads/" + req.file.originalname, function(err) {
        if (err) {
            throw err;
        }

        var data = req.file
        console.log(data);

        var file = req.file.path;


        //添加到数据库

        const query = `insert into novelMeta(file) values( '${file}')`;
        console.log(query);
        db.query(query, (err, results) => {
            if (err) {
                console.log(err.message);
                res.send({
                    status: 501,
                    msg: '执行Sql报错',
                })
            } else {
                res.send({
                    code: 200,
                    msg: '上传成功',
                    data: results
                })
            }
        })

    })
}

//身份认证
exports.userMsg = (req, res) => {
    console.log('身份认证');
    let loginName = req.query.loginName;
    // console.log(loginName);
    let str = `select * from loginForm where loginName = '${loginName}'`;
    db.query(str, (err, result) => {
        if (err) {
            res.send({
                status: 501,
                msg: '执行Sql报错'
            })
        } else {
            res.send({
                status: 200,
                msg: '获取成功',
                data: result,
            })
        }
    })
}

//查询表格
exports.getBookByParams = (req, res) => {
    console.log(req.query);
    let str;
    if (req.query.titleID) {
        str = `select * from novelMeta where titleID = ${req.query.titleID}`;
    } else if (req.query.title) {
        str = `select * from novelMeta where title = ${req.query.title}`
    } else if (req.query.author) {
        str = `select * from novelMeta where author = ${req.query.author}`
    } else if (req.query.label) {
        str = `select * from novelMeta where label = ${req.query.label}`
    }

    db.query(str, (err, results) => {
        if (err) {
            console.log(err.message);
            res.send({
                status: 500,
                msg: '获取失败！',
            })
        } else {
            // console.log(results);
            res.send({
                status: 200,
                msg: '获取成功',
                data: results,
            })
        }
    })


}

//渲染图书列表
exports.getbooks = (req, res) => {
        console.log('in getbooks function');
        db.query('select * from novelMeta where status is null', (err, results) => {
            if (err) {
                // console.log(err.message);
                res.send({
                    status: 500,
                    msg: '获取图书列表失败！',
                })
            } else {
                // console.log(results);
                res.send({
                    status: 200,
                    msg: '获取图书列表成功',
                    data: results,
                })
            }

        })

    }
    //删除图书
exports.delbook = (req, res) => {
    console.log('in delbook function');
    let id = req.body.id;
    let sum = '';
    let str = '';
    let str2 = '';
    if (!req.body.id) {
        res.send({
            status: 500,
            msg: '未指定要删除的图书Id',
        })
        return;
    } else if (id.length == 1) {
        str = `select * from novelMeta where id = ${id[0]}`;
        str2 = `update novelMeta set status = 1 where id = ${id[0]}`
    } else {
        for (let i = 1; i < id.length; i++) {
            sum += ' or id =' + id[i];
            str = `select * from novelMeta where id = ${id[0]}` + sum;
            str2 = `update novelMeta set status = 1 where id = ${id[0]}` + sum;
        }
    }
    db.query(str, (err, results) => {
        if (err) {
            console.log(err.message);
            res.send({
                status: 501,
                msg: '执行Sql报错',
            })
        } else {
            if (results.length == 0) {
                res.send({
                    status: 502,
                    msg: '要删除的图书不存在',
                })
            } else {
                realdelbook(str2, res);
            }
        }
    })
}

function realdelbook(str2, res) {
    db.query(str2, (err, result) => {
        if (err) {
            res.send({
                status: 501,
                msg: '执行Sql报错',
            })
            return;
        } else if (result.affectedRows == 1) {
            // console.log(result);
            res.send({
                status: 200,
                msg: '删除图书成功！',
            })
            return;
        } else {
            res.send({
                status: 200,
                msg: '删除图书成功！',
            })
            return;
        }
    })
}

//添加图书
exports.addbook = (req, res) => {
    console.log(req.body);
    const book = req.body
    db.query('insert into novelMeta set ?', book, (err, results) => {
        if (err) {
            res.send({
                status: 501,
                msg: '执行Sql报错',
            })
        } else if (results.affectedRows != 1) {
            res.send({
                status: 500,
                msg: '添加失败',
            })
        } else {
            res.send({
                status: 201,
                msg: '添加成功',
            })
        }
    })
}

//查询图书
exports.querybook = (req, res) => {
    console.log(req.body);
    let keys = [];
    let values = [];
    for (let k in req.body) {
        keys.push(k);
        values.push(req.body[k]);
    }
    console.log(keys);
    console.log(values);
    if (keys.length == 0) {
        return;
    } else if (keys.length == 1) {
        var str = `select * from books where ${keys[0]} like '%${values[0]}%'`;
    } else if (keys.length == 2) {
        var str = `select * from books where ${keys[0]} like '%${values[0]}%' and ${keys[1]} like '%${values[1]}%'`;
    } else if (keys.length == 3) {
        var str = `select * from books where ${keys[0]} like '%${values[0]}%' and ${keys[1]} like '%${values[1]}%' and ${keys[2]} like '%${values[2]}%'`;
    }
    db.query(str, (err, results) => {
        if (err) {
            console.log(results);
            res.send({
                status: 501,
                msg: '执行Sql报错',
            })
        } else {
            console.log(results);
            res.send({
                status: 201,
                msg: '查询成功',
                data: results,
            })
        }
    })
}

//修改图书信息
exports.editbook = (req, res) => {
    console.log(req.body);
    const id = req.body.id;
    const isDefault = req.body.isDefault;
    db.query(`update novelMeta set isDefault = '${isDefault}' where id = ${id}`, (err, results) => {
        if (err) {
            res.send({
                status: 501,
                msg: '执行Sql报错',
            })
        } else {
            res.send({
                status: 201,
                msg: '查询成功',
            })
        }
    })
}

exports.alldelbook = (req, res) => {
    db.query('update books set status = 1', (err, results) => {
        if (err) {
            console.log(err.message);
            res.send({
                status: 501,
                msg: '执行Sql报错',
            })
        } else {
            res.send({
                status: 201,
                msg: '查询成功',
            })
        }
    })
}

//获取全部关键词
exports.getKeywords = (req, res) => {
    console.log('in getKeywords function');
    let str = `select * from keywordsBase where status is null`;
    db.query(str, (err, results) => {
        if (err) {
            res.send({
                status: 501,
                msg: '获取关键词失败'
            })
        } else {
            res.send({
                status: 200,
                msg: '获取关键词成功',
                data: results,
            })
        }
    })
}